/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cn.com.jsepc.rep.process;

import cn.com.jsepc.rep.comm.ConstUtil;
import cn.com.jsepc.rep.vo.PointGridVO;
import cn.com.jsepc.rep.vo.Relate_RepVO;
import com.jarp.db.DAO;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.List;
import java.util.Vector;
import net.sf.json.JSONArray;

/**
 *
 * @author zhaoyuan
 */
public class GetPointRep {
    
    public String GetAll(String start, String limit){
        String res="";
        DAO dao=new DAO();
        try {
            List<PointGridVO> lstResults = new ArrayList<PointGridVO>();
            int iStart = Integer.parseInt(start);
            int iPageSize = Integer.parseInt(limit);
            int iPage = iStart / iPageSize + 1;
            StringBuffer str = new StringBuffer();
            str.append("select b.repname,b.repcode,d.cataname,a.pointid,c.pointname,a.refid from tb_point_rep a "
                    + " LEFT JOIN TB_POINT c on c.pointid=a.pointid "
                    + " left join tb_report b on b.repID=a.repID "
                    + " left join TB_CATALOG d on d.cataid=b.cataid");
            Vector v = dao.getDataSet(ConstUtil.DBNAME, str.toString(), iPage, iPageSize);
            if (v != null) {
                for (int i = 0; i < v.size(); i++) {
                    Hashtable hashRows = (Hashtable) v.get(i);
                    PointGridVO repVO = new PointGridVO();
                    repVO.setRefid((String) hashRows.get("REFID"));
                    repVO.setRepid((String) hashRows.get("REPID"));
                    repVO.setRepcode((String) hashRows.get("REPCODE"));
                    repVO.setRepname((String) hashRows.get("REPNAME"));
                    repVO.setCataname((String) hashRows.get("CATANAME"));
                    repVO.setPointname((String) hashRows.get("POINTNAME"));
                    repVO.setPointid((String) hashRows.get("POINTID"));
                    lstResults.add(repVO);
                } }
            JSONArray json = new JSONArray();
            StringBuffer str1 = new StringBuffer();
            str1.append("select count(1) CNT from tb_point_rep");
            String totalCount = dao.getDataValue(ConstUtil.DBNAME, str1.toString());
            json = JSONArray.fromObject(lstResults);
            res = "{totalCount:" + totalCount + ",data:" + json.toString()
                    + "}";
           
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        
        return res;
    }
    
    public String FromDomain(String start, String limit,String domainid){
        String [] mystr=domainid.split("_");
        domainid=mystr[mystr.length-1];
        String res="";
        DAO dao=new DAO();
        try {
            List<PointGridVO> lstResults = new ArrayList<PointGridVO>();
            int iStart = Integer.parseInt(start);
            int iPageSize = Integer.parseInt(limit);
            int iPage = iStart / iPageSize + 1;
            StringBuffer str = new StringBuffer();
            str.append("select g.repname,g.repcode,h.cataname,e.pointid,e.pointname,f.refid from TB_DOMAIN a LEFT JOIN TB_PROCESS b ON a.DOMAINID=b.domainid "
                    + " LEFT JOIN TB_PROCESS_LIFECYCLE c ON c.processid=b.processid "
                    + " LEFT JOIN TB_LINK d ON d.LIFECYCLEID=c.LIFECYCLEID"
                    + " LEFT JOIN TB_POINT e ON e.linkid=d.linkid"
                    + " left join tb_point_rep f on f.pointid=e.pointid"
                    + " left join tb_report g on g.repID=f.repID "
                    + " left join TB_CATALOG h on h.cataid=g.cataid"
                    + " WHERE a.DOMAINID="+domainid+" AND f.pointid is NOT NULL");
            
            Vector v = dao.getDataSet(ConstUtil.DBNAME, str.toString(), iPage, iPageSize);
            if (v != null) {
                for (int i = 0; i < v.size(); i++) {
                    Hashtable hashRows = (Hashtable) v.get(i);
                    PointGridVO repVO = new PointGridVO();
                    repVO.setRefid((String) hashRows.get("REFID"));
                    repVO.setRepid((String) hashRows.get("REPID"));
                    repVO.setRepcode((String) hashRows.get("REPCODE"));
                    repVO.setRepname((String) hashRows.get("REPNAME"));
                    repVO.setCataname((String) hashRows.get("CATANAME"));
                    repVO.setPointname((String) hashRows.get("POINTNAME"));
                    repVO.setPointid((String) hashRows.get("POINTID"));
                    lstResults.add(repVO);
                }  }
            JSONArray json = new JSONArray();
            StringBuffer str1 = new StringBuffer();
            str1.append("select count(1) from TB_DOMAIN a LEFT JOIN TB_PROCESS b ON a.DOMAINID=b.domainid "
                    + " LEFT JOIN TB_PROCESS_LIFECYCLE c ON c.processid=b.processid "
                    + " LEFT JOIN TB_LINK d ON d.LIFECYCLEID=c.LIFECYCLEID"
                    + " LEFT JOIN TB_POINT e ON e.linkid=d.linkid"
                    + " left join tb_point_rep f on f.pointid=e.pointid"
                    + " left join tb_report g on g.repID=f.repID "
                    + " left join TB_CATALOG h on h.cataid=g.cataid"
                    + " WHERE a.DOMAINID="+domainid+" AND f.pointid is NOT NULL");
            String totalCount = dao.getDataValue(ConstUtil.DBNAME, str1.toString());
            json = JSONArray.fromObject(lstResults);
            res = "{totalCount:" + totalCount + ",data:" + json.toString()
                    + "}";
          
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        
        return res;        
    }
    
    public String FromProcess(String start, String limit,String processid){
        String [] mystr=processid.split("_");
        processid=mystr[mystr.length-1];
        String res="";
        DAO dao=new DAO();
        try {
            List<PointGridVO> lstResults = new ArrayList<PointGridVO>();
            int iStart = Integer.parseInt(start);
            int iPageSize = Integer.parseInt(limit);
            int iPage = iStart / iPageSize + 1;
            StringBuffer str = new StringBuffer();
            str.append("select g.repname,g.repcode,h.cataname,e.pointid,e.pointname,f.refid from  TB_PROCESS b  "
                    + " LEFT JOIN TB_PROCESS_LIFECYCLE c ON c.processid=b.processid "
                    + " LEFT JOIN TB_LINK d ON d.LIFECYCLEID=c.LIFECYCLEID"
                    + " LEFT JOIN TB_POINT e ON e.linkid=d.linkid"
                    + " left join tb_point_rep f on f.pointid=e.pointid"
                    + " left join tb_report g on g.repID=f.repID "
                    + " left join TB_CATALOG h on h.cataid=g.cataid"
                    + " WHERE b.processid="+processid+" AND f.pointid is NOT NULL");
            Vector v = dao.getDataSet(ConstUtil.DBNAME, str.toString(), iPage, iPageSize);
            if (v != null) {
                for (int i = 0; i < v.size(); i++) {
                    Hashtable hashRows = (Hashtable) v.get(i);
                    PointGridVO repVO = new PointGridVO();
                    repVO.setRefid((String) hashRows.get("REFID"));
                    repVO.setRepid((String) hashRows.get("REPID"));
                    repVO.setRepcode((String) hashRows.get("REPCODE"));
                    repVO.setRepname((String) hashRows.get("REPNAME"));
                    repVO.setCataname((String) hashRows.get("CATANAME"));
                    repVO.setPointname((String) hashRows.get("POINTNAME"));
                    repVO.setPointid((String) hashRows.get("POINTID"));
                    lstResults.add(repVO);
                }}
            JSONArray json = new JSONArray();
            StringBuffer str1 = new StringBuffer();
            str1.append("select count(1) from  TB_PROCESS b  "
                    + " LEFT JOIN TB_PROCESS_LIFECYCLE c ON c.processid=b.processid "
                    + " LEFT JOIN TB_LINK d ON d.LIFECYCLEID=c.LIFECYCLEID"
                    + " LEFT JOIN TB_POINT e ON e.linkid=d.linkid"
                    + " left join tb_point_rep f on f.pointid=e.pointid"
                    + " left join tb_report g on g.repID=f.repID "
                    + " left join TB_CATALOG h on h.cataid=g.cataid"
                    + " WHERE b.processid="+processid+" AND f.pointid is NOT NULL");
            String totalCount = dao.getDataValue(ConstUtil.DBNAME, str1.toString());
            json = JSONArray.fromObject(lstResults);
            res = "{totalCount:" + totalCount + ",data:" + json.toString()
                    + "}";
            
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        
        return res;        
    }
    
    public String FromLife(String start, String limit,String LIFECYCLEID){
        String [] mystr=LIFECYCLEID.split("_");
        LIFECYCLEID=mystr[mystr.length-1];
        String res="";
        DAO dao=new DAO();
        try {
            List<PointGridVO> lstResults = new ArrayList<PointGridVO>();
            int iStart = Integer.parseInt(start);
            int iPageSize = Integer.parseInt(limit);
            int iPage = iStart / iPageSize + 1;
            StringBuffer str = new StringBuffer();
            str.append("select g.repname,g.repcode,h.cataname,e.pointid,e.pointname,f.refid from  TB_PROCESS_LIFECYCLE c  "
                    + " LEFT JOIN TB_LINK d ON d.LIFECYCLEID=c.LIFECYCLEID"
                    + " LEFT JOIN TB_POINT e ON e.linkid=d.linkid"
                    + " left join tb_point_rep f on f.pointid=e.pointid"
                    + " left join tb_report g on g.repID=f.repID "
                    + " left join TB_CATALOG h on h.cataid=g.cataid"
                    + " WHERE c.LIFECYCLEID="+LIFECYCLEID+" AND f.pointid is NOT NULL");
            
            Vector v = dao.getDataSet(ConstUtil.DBNAME, str.toString(), iPage, iPageSize);
            if (v != null) {
                for (int i = 0; i < v.size(); i++) {
                    Hashtable hashRows = (Hashtable) v.get(i);
                    PointGridVO repVO = new PointGridVO();
                    repVO.setRefid((String) hashRows.get("REFID"));
                    repVO.setRepid((String) hashRows.get("REPID"));
                    repVO.setRepcode((String) hashRows.get("REPCODE"));
                    repVO.setRepname((String) hashRows.get("REPNAME"));
                    repVO.setCataname((String) hashRows.get("CATANAME"));
                    repVO.setPointname((String) hashRows.get("POINTNAME"));
                    repVO.setPointid((String) hashRows.get("POINTID"));
                    lstResults.add(repVO);
                }}
            JSONArray json = new JSONArray();
            StringBuffer str1 = new StringBuffer();
            str1.append("select count(1) from  TB_PROCESS_LIFECYCLE c  "
                    + " LEFT JOIN TB_LINK d ON d.LIFECYCLEID=c.LIFECYCLEID"
                    + " LEFT JOIN TB_POINT e ON e.linkid=d.linkid"
                    + " left join tb_point_rep f on f.pointid=e.pointid"
                    + " left join tb_report g on g.repID=f.repID "
                    + " left join TB_CATALOG h on h.cataid=g.cataid"
                    + " WHERE c.LIFECYCLEID="+LIFECYCLEID+" AND f.pointid is NOT NULL");
            String totalCount = dao.getDataValue(ConstUtil.DBNAME, str1.toString());
            json = JSONArray.fromObject(lstResults);
            res = "{totalCount:" + totalCount + ",data:" + json.toString()
                    + "}";
            
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        
        return res;        
    }
    public String FromLink(String start, String limit,String LINKID){
        String [] mystr=LINKID.split("_");
        LINKID=mystr[mystr.length-1];
        String res="";
        DAO dao=new DAO();
        try {
            List<PointGridVO> lstResults = new ArrayList<PointGridVO>();
            int iStart = Integer.parseInt(start);
            int iPageSize = Integer.parseInt(limit);
            int iPage = iStart / iPageSize + 1;
            StringBuffer str = new StringBuffer();
            str.append("select g.repname,g.repcode,h.cataname,e.pointid,e.pointname,f.refid from  TB_LINK d "
                    + " LEFT JOIN TB_POINT e ON e.linkid=d.linkid"
                    + " left join tb_point_rep f on f.pointid=e.pointid"
                    + " left join tb_report g on g.repID=f.repID "
                    + " left join TB_CATALOG h on h.cataid=g.cataid"
                    + " WHERE d.LINKID="+LINKID+" AND f.pointid is NOT NULL");
            
            Vector v = dao.getDataSet(ConstUtil.DBNAME, str.toString(), iPage, iPageSize);
            if (v != null) {
                for (int i = 0; i < v.size(); i++) {
                    Hashtable hashRows = (Hashtable) v.get(i);
                    PointGridVO repVO = new PointGridVO();
                    repVO.setRefid((String) hashRows.get("REFID"));
                    repVO.setRepid((String) hashRows.get("REPID"));
                    repVO.setRepcode((String) hashRows.get("REPCODE"));
                    repVO.setRepname((String) hashRows.get("REPNAME"));
                    repVO.setCataname((String) hashRows.get("CATANAME"));
                    repVO.setPointname((String) hashRows.get("POINTNAME"));
                    repVO.setPointid((String) hashRows.get("POINTID"));
                    lstResults.add(repVO);
                }}
            JSONArray json = new JSONArray();
            StringBuffer str1 = new StringBuffer();
            str1.append("select count(1) from   TB_LINK d "
                    + " LEFT JOIN TB_POINT e ON e.linkid=d.linkid"
                    + " left join tb_point_rep f on f.pointid=e.pointid"
                    + " left join tb_report g on g.repID=f.repID "
                    + " left join TB_CATALOG h on h.cataid=g.cataid"
                    + " WHERE d.LINKID="+LINKID+" AND f.pointid is NOT NULL");
            String totalCount = dao.getDataValue(ConstUtil.DBNAME, str1.toString());
            json = JSONArray.fromObject(lstResults);
            res = "{totalCount:" + totalCount + ",data:" + json.toString()
                    + "}";
            
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        
        return res;        
    }
    
    public String FromPoint(String start, String limit,String Pointid){
        String [] mystr=Pointid.split("_");
        Pointid=mystr[mystr.length-1];
        String res="";
        DAO dao=new DAO();
        try {
            List<PointGridVO> lstResults = new ArrayList<PointGridVO>();
            int iStart = Integer.parseInt(start);
            int iPageSize = Integer.parseInt(limit);
            int iPage = iStart / iPageSize + 1;
            StringBuffer str = new StringBuffer();
            str.append("select g.repname,g.repcode,h.cataname,e.pointid,e.pointname,f.refid from TB_POINT e"
                    + " left join tb_point_rep f on f.pointid=e.pointid"
                    + " left join tb_report g on g.repID=f.repID "
                    + " left join TB_CATALOG h on h.cataid=g.cataid"
                    + " WHERE e.POINTID="+Pointid+" AND f.pointid is NOT NULL");
            
            Vector v = dao.getDataSet(ConstUtil.DBNAME, str.toString(), iPage, iPageSize);
            if (v != null) {
                for (int i = 0; i < v.size(); i++) {
                    Hashtable hashRows = (Hashtable) v.get(i);
                    PointGridVO repVO = new PointGridVO();
                    repVO.setRefid((String) hashRows.get("REFID"));
                    repVO.setRepid((String) hashRows.get("REPID"));
                    repVO.setRepcode((String) hashRows.get("REPCODE"));
                    repVO.setRepname((String) hashRows.get("REPNAME"));
                    repVO.setCataname((String) hashRows.get("CATANAME"));
                    repVO.setPointname((String) hashRows.get("POINTNAME"));
                    repVO.setPointid((String) hashRows.get("POINTID"));
                    lstResults.add(repVO);
                }}
            JSONArray json = new JSONArray();
            StringBuffer str1 = new StringBuffer();
            str1.append("select count(1) from   TB_LINK d "
                    + " LEFT JOIN TB_POINT e ON e.linkid=d.linkid"
                    + " left join tb_point_rep f on f.pointid=e.pointid"
                    + " left join tb_report g on g.repID=f.repID "
                    + " left join TB_CATALOG h on h.cataid=g.cataid"
                    + " WHERE e.POINTID="+Pointid+" AND f.pointid is NOT NULL");
            String totalCount = dao.getDataValue(ConstUtil.DBNAME, str1.toString());
            json = JSONArray.fromObject(lstResults);
            res = "{totalCount:" + totalCount + ",data:" + json.toString()
                    + "}";
            
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        
        return res;        
    }
    
}
